from PyQt6.QtWidgets import QApplication, QMainWindow, QTableWidgetItem
import sys
import pandas as pd
from hello import Ui_MainWindow  # 替换为你的UI文件名
import pywencai
from datetime import datetime as dt
import redis
import pickle
from sqlalchemy import create_engine
from clickhouse_driver import Client
import logging
import akshare as ak
from PyQt6.QtCore import QDate
from PyQt6.QtCore import QThread, pyqtSignal
from PyQt6.QtCore import QTimer, QDateTime
import sys
import threading
import easyquotation
import traceback
import pymysql
pymysql.install_as_MySQLdb()
# 设置全局异常捕获
def excepthook(type, value, tback):
    logging.error("Uncaught exception:", exc_info=(type, value, tback))
    print("".join(traceback.format_exception(type, value, tback)))
    sys.__excepthook__(type, value, tback)

sys.excepthook = excepthook

client = Client('192.168.8.100', port=9000, user='default', password='dgls', database='stock')

log_format = "%(asctime)s - %(levelname)s - %(process)d - %(filename)s:%(lineno)d - %(message)s"
date_format = "%Y-%m-%d %H:%M:%S"  # 精确到秒
logging.basicConfig(level=logging.DEBUG, format=log_format, datefmt=date_format)

engine = create_engine('clickhouse+http://default:dgls@192.168.8.100:8123/stock')

redis_host = "192.168.8.100"
redis_port = 6379
redis_db = 0
redis_password = "DRC@1234bank"
r = redis.Redis(host=redis_host, port=redis_port, db=redis_db, password=redis_password)

redis_host_remote = "www.canstock.site"
redis_port_remote = 6379
redis_db_remote = 0
redis_password_remote = "DRC@1234bank"
r_remote = redis.Redis(host=redis_host_remote, port=6379, db=0, password=redis_password_remote)


mysql_port = 3306
mysql_host = "43.139.186.19"
mysql_db = "stock"
import urllib.parse
mysql_password = urllib.parse.quote("DRC@1234bank")
mysql_user = "root"
db_url = f'mysql://{mysql_user}:{mysql_password}@{mysql_host}:{mysql_port}/{mysql_db}'

mysql_engine = create_engine(db_url,pool_size=20,max_overflow=20,pool_recycle=60)



def get_pre_trade_date(trade_date):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] < pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=False)
    pre_trade_date = date_df["trade_date"].values[0]
    return pre_trade_date.strftime("%Y%m%d")


def get_pre_trade_date_n(trade_date, n):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] < pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=False)
    pre_trade_date = date_df["trade_date"].values[n - 1]
    return pre_trade_date.strftime("%Y%m%d")


class RedisListenerThread(QThread):
    new_data_signal = pyqtSignal(pd.DataFrame)  # 创建一个信号，用来通知主线程有新数据

    def __init__(self, redis_client, channel):
        super().__init__()

        self.redis_client = redis_client
        self.channel = channel
        self.running = True

    def run(self):
        pubsub = self.redis_client.pubsub()
        pubsub.subscribe(self.channel)

        for message in pubsub.listen():
            if not self.running:
                break
            if message['type'] == 'message':
                increase_df = pickle.loads(message['data'])
                self.new_data_signal.emit(increase_df)

    def stop(self):
        self.running = False

class RedisListenerThread(QThread):
    new_data_signal = pyqtSignal(pd.DataFrame)  # 创建一个信号，用来通知主线程有新数据

    def __init__(self, redis_client, channel):
        super().__init__()

        self.redis_client = redis_client
        self.channel = channel
        self.running = True

    def run(self):
        pubsub = self.redis_client.pubsub()
        pubsub.subscribe(self.channel)

        for message in pubsub.listen():
            if not self.running:
                break
            if message['type'] == 'message':
                increase_df = pickle.loads(message['data'])
                self.new_data_signal.emit(increase_df)

    def stop(self):
        self.running = False
class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()
        # 创建UI对象
        self.ui = Ui_MainWindow()
        # 设置UI
        self.ui.setupUi(self)

        # 加载Excel数据到表格中
        self.load_excel_data(r'../股票概念整理.xlsx')
        self.load_dixi_data(dt.now().strftime('%Y%m%d'))
        self.init_dxjl_data()

        self.quotation = easyquotation.use('tencent')
        # 连接按钮的点击事件到槽函数
        self.ui.pushButton.clicked.connect(self.on_button_click)
        self.ui.pushButton_2.clicked.connect(self.on_dixi_search_click)

        # 连接“专”按钮的切换事件到筛选函数
        self.ui.radioButton.toggled.connect(self.on_radio_button_toggled)

        # 连接“专”按钮的切换事件到筛选函数
        self.ui.dxjl_analysis_radiobutton.toggled.connect(self.dxjl_analysis_radiobutton_toggled)


        # 设置当前日期为默认日期
        today = QDate.currentDate()
        self.ui.dateEdit.setDate(today)
        self.ui.dateEdit_2.setDate(today)

        # 启动Redis监听线程
        self.redis_listener_thread = RedisListenerThread(r_remote, 'dxjl_channel')
        self.redis_listener_thread.new_data_signal.connect(self.update_dxjl_data)
        self.redis_listener_thread.start()

        # 创建并启动定时器来更新时间
        self.time_timer = QTimer(self)
        self.time_timer.timeout.connect(self.update_time)  # 连接定时器的timeout信号到update_time槽
        self.time_timer.start(1000)  # 设置定时器每秒触发一次


        # 创建市场数据更新定时器，但不启动
        self.market_timer = QTimer(self)
        self.market_timer.timeout.connect(self.update_market_data)

    def init_dxjl_data(self):
        current_date = dt.now().strftime('%Y%m%d')
        sql = f"select * from real_market_info_dxjl rmid where date(`timestamp`)='{current_date}' order by timestamp desc"
        self.dxjl_df = pd.read_sql(sql,mysql_engine)


        sql = f"select * from stock.stock_fupan sf where `date` ='{current_date}'"
        self.stock_fupan_df  = pd.read_sql(sql,engine)

        rdf = pd.merge(self.df, self.dxjl_df, how="inner", left_on="股票名称", right_on="stock_name")
        rdf = pd.merge(self.stock_fupan_df[["stock_name","stock_code","consecutive_limit_up_days","market_value","limit_up_times_in_last_year"]], rdf, how="inner", left_on="stock_name", right_on="stock_name")
        rdf = rdf[["timestamp","股票名称","trade_type","一级核心概念","一级核心","二级核心概念","二级核心程度","备注","consecutive_limit_up_days","market_value","limit_up_times_in_last_year"]]


        rdf = rdf.sort_values(by="timestamp",ascending=False)
        self.display_data(self.ui.tableWidget_4,rdf)

    def on_radio_button_toggled(self, checked):
        if checked:
            # 选中时进行筛选
            filtered_df = self.df[self.df["一级核心"].str.contains("专")]
        else:
            # 未选中时显示全部数据
            filtered_df = self.df

        self.display_data(self.ui.tableWidget,filtered_df)

    def dxjl_analysis_radiobutton_toggled(self,checked):
        if checked:
            # 启动市场数据更新定时器，每秒更新一次
            self.market_timer.start(1000)
        else:
            # 停止市场数据更新定时器
            self.market_timer.stop()
            self.init_dxjl_data()



    def display_data(self,table ,df):
        if len(df)<=0:
            return
        # 设置表格的行数和列数
        table.setRowCount(len(df))
        table.setColumnCount(len(df.columns))

        # 设置表头
        table.setHorizontalHeaderLabels(df.columns)

        # 填充表格数据
        for row in range(len(df)):
            for col in range(len(df.columns)):
                item = QTableWidgetItem(str(df.iat[row, col]))
                table.setItem(row, col, item)

    def on_dixi_search_click(self):
        selected_date = self.ui.dateEdit_2.date().toPyDate()
        formatted_date = selected_date.strftime('%Y%m%d')
        self.load_dixi_data(formatted_date)

    def on_button_click(self):
        selected_date = self.ui.dateEdit.date().toPyDate()
        formatted_date = selected_date.strftime('%Y%m%d')
        query = f"{formatted_date}涨停,市值,连续涨停天数"

        df = pywencai.get(query=query, loop=True)

        rdf = pd.merge(self.df, df, how="inner", left_on="股票名称", right_on="股票简称")
        rdf.rename({f"连续涨停天数[{formatted_date}]": "连续涨停天数"}, axis=1, inplace=True)
        rdf.rename({f"a股市值(不含限售股)[{formatted_date}]": "流通市值"}, axis=1, inplace=True)
        rdf["流通市值"] = rdf["流通市值"].astype(float)
        rdf["流通市值"] = round(rdf["流通市值"] / 100000000, 2)
        rdf = self.df[
            ["股票代码", "股票简称", "流通市值", "一级核心概念", "一级核心", "二级核心概念", "二级核心程度", "备注",
             "连续涨停天数"]]
        rdf = self.df.sort_values(by="一级核心概念")
        self.display_data(self.ui.tableWidget,rdf)

    def update_dxjl_data(self, new_data):
        new_data['timestamp'] = pd.to_datetime(new_data['timestamp'])
        self.dxjl_df = pd.concat([self.dxjl_df, new_data])
        rdf = pd.merge(self.df, self.dxjl_df, how="inner", left_on="股票名称", right_on="stock_name")
        rdf = pd.merge(self.stock_fupan_df[["stock_name","stock_code","consecutive_limit_up_days","market_value","limit_up_times_in_last_year"]], rdf, how="inner", left_on="stock_name", right_on="stock_name")
        # rdf = pd.merge(rdf, self.tdx_df, how="inner", left_on="stock_name", right_on="name")
        rdf = rdf[["timestamp","股票名称","trade_type","一级核心概念","一级核心","二级核心概念","二级核心程度","备注","consecutive_limit_up_days","market_value","limit_up_times_in_last_year"]]
        rdf = rdf.sort_values(by="timestamp",ascending=False)
        self.display_data(self.ui.tableWidget_4,rdf)

    def load_excel_data(self, file_path):
        self.df = pd.read_excel(file_path)
        self.display_data(self.ui.tableWidget,self.df)

    def load_dixi_data(self, query_day):
        one_year_before = get_pre_trade_date(query_day)
        pre_3_day = get_pre_trade_date_n(query_day, 5)

        df_tdx = pd.read_sql(
            f'''SELECT a.*,a.amount t_amount FROM stock.stock_daily a WHERE name NOT LIKE '%%ST%%' and rps3 >99 AND trade_date = '{one_year_before}'
            and name not in (select name from stock.stock_daily a WHERE trade_date >= '{pre_3_day}' and trade_date<'{one_year_before}' and rps3>98 and low<ma5)
            ''',
            engine
        )

        if query_day == dt.now().strftime('%Y%m%d'):
            df = pickle.loads(r.get("real_stock_info_tdx"))
        else:
            df = pd.read_sql(f"select a.* from stock.stock_daily a where trade_date ='{query_day}'", engine)
            df["change"] = 0
            df["price"] = 0

        df_tdx = pd.merge(df_tdx[["code", "name", "ma5", "rps3", "t_amount"]], df[["code", "open", "price", "change"]],
                          how="inner")
        df_tdx["ma5"] = (df_tdx["ma5"] * 4 + df_tdx["open"]) / 5
        df_tdx["t_amount"] = round(df_tdx["t_amount"] / 10000 / 10000, 2)
        df_tdx["bais5"] = round((df_tdx["open"] - df_tdx["ma5"]) * 100 / df_tdx["ma5"], 2)
        df_tdx = df_tdx.query("bais5<5 and bais5>-50 and t_amount>5")
        df_tdx = df_tdx[["code", "name", "bais5", "rps3", "t_amount", "price", "change"]]
        df_tdx = pd.merge(df_tdx, self.df, how="inner", left_on="name", right_on="股票名称")
        del df_tdx['name']
        df_tdx = df_tdx.sort_values(by="rps3", ascending=False)

        self.ui.tableWidget_2.setRowCount(len(df_tdx))
        self.ui.tableWidget_2.setColumnCount(len(df_tdx.columns))
        self.ui.tableWidget_2.setHorizontalHeaderLabels(df_tdx.columns)
        for row in range(len(df_tdx)):
            for col in range(len(df_tdx.columns)):
                item = QTableWidgetItem(str(df_tdx.iat[row, col]))
                self.ui.tableWidget_2.setItem(row, col, item)



    def update_time(self):
        # 获取当前时间并格式化为字符串
        current_time = QDateTime.currentDateTime().toString("yyyy-MM-dd HH:mm:ss")
        # 在QLabel中显示当前时间
        self.ui.time_label.setText(current_time)

    def update_market_data(self):
        try:

            rdf = self.dxjl_df.query("trade_type in ('笼子触涨停','逼近涨停')")
            rdf = rdf.sort_values(by=['stock_name', 'timestamp'], ascending=[True, False])
            rdf = rdf.drop_duplicates(subset=['stock_name'], keep='first')

            rdf = pd.merge(rdf,self.df[["股票名称","一级核心概念","一级核心"]],left_on="stock_name",right_on="股票名称")
            rdf = pd.merge(rdf, self.stock_fupan_df[["stock_name", "stock_code","consecutive_limit_up_days"]],left_on="stock_name",right_on="stock_name")

            rdf = rdf.query("consecutive_limit_up_days==0")


            res = self.quotation.stocks(rdf["stock_code"].to_list())
            data_list = [res[t] for t in res]
            self.tdx_df = pd.DataFrame(data_list)


            rdf = pd.merge(rdf,self.tdx_df,how="inner",left_on="stock_name",right_on="name")
            rdf = rdf[['timestamp','name', 'code',"一级核心概念","一级核心","consecutive_limit_up_days",
           '涨跌(%)', '成交额(万)', '流通市值', '总市值']]
            rdf["成交额(万)"] = rdf["成交额(万)"]/10000
            rdf = rdf.sort_values(by="timestamp", ascending=False)




            self.display_data(self.ui.tableWidget_4, rdf)


        except Exception as e:
            logging.error("Error fetching market data: ", exc_info=True)

    def closeEvent(self, event):
        # 当窗口关闭时，停止线程
        self.redis_listener_thread.stop()
        # self.redis_listener_thread.wait()
        event.accept()

# 主程序入口
if __name__ == "__main__":
    try:
        app = QApplication(sys.argv)
        main_window = MainWindow()
        main_window.show()
        sys.exit(app.exec())
    except Exception as e:
        logging.error("An error occurred in the main block: ", exc_info=True)
        print(f"An unexpected error occurred: {e}")
        traceback.print_exc()  # 打印详细的堆栈信息
